// ================================================================================
// 		File: PartyMeetingLecture.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-03-30 20:25
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 三会一课
    /// </summary>   
    public class PartyMeetingLecture : EntityBase
    {
        #region 属性


        /// <summary>
        /// 组织ID
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 标题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 类型
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 会议类型
        /// </summary>
        public string MeetType { get; set; }

        /// <summary>
        /// 简介
        /// </summary>
        public string Summary { get; set; }

        /// <summary>
        /// 开始时间
        /// </summary>
        public DateTime? BeginTime { get; set; }

        /// <summary>
        /// 结束时间
        /// </summary>
        public DateTime? EndTime { get; set; }

        /// <summary>
        /// 地点
        /// </summary>
        public string Address { get; set; }

        /// <summary>
        /// 地图坐标
        /// </summary>
        public string Coordinate { get; set; }

        /// <summary>
        /// 短信通知
        /// </summary>
        public string IsSmsAlert { get; set; }

        /// <summary>
        /// 短信通知内容
        /// </summary>
        public string SmsContent { get; set; }

        /// <summary>
        /// 图片ID
        /// </summary>
        public string ImageID { get; set; }

        /// <summary>
        /// 是否结束
        /// </summary>
        public bool IsEnd { get; set; }

        /// <summary>
        /// 记录
        /// </summary>
        public string Record { get; set; }

        /// <summary>
        /// 0、无；1、置顶；2、固顶；3、置顶+固顶；
        /// </summary>
        public int Flags { get; set; }

        /// <summary>
        /// 发布状态( 1 草稿 2 创建 4发布 )
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 审核状态(1审核中 2驳回  4审核通过)
        /// </summary>
        public int Aduit { get; set; }

        /// <summary>
        /// 是否已推送到上级
        /// </summary>
        public string IsPushUp { get; set; }

        /// <summary>
        /// 源ID
        /// </summary>
        public string RelationID { get; set; }

        /// <summary>
        /// 修改用户
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 创建用户
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 是否是手机端
        /// </summary>
        public int? IsPhone { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string category, string title, string branchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,Title,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,BranchName,MeetType,Category
                        from tblpartymeetinglecture a
                        left join tblpartyorgbranch b on a.BranchID=b.BranchID
                        where 1=1 ");

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category like ?Category ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = string.Format("%{0}%", category) });
            }
            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and Title like ?Title ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID=?BranchID ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = string.Format("{0}", branchId) });
            }

            sb.Append(" order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }




        //课堂和会议的活动记录
        public static DataSet GetGridData1(int pageIndex, int pageSize, string branchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,Title,a.BranchID,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,BranchName,MeetType,case Category when 'Class' then 'tblPartyMyCourses' when 'Meeting' then 'tblPartyMyMeeting' when 'Class+Meeting' then 'tblPartyMyCourses' end as Category
                        from tblpartymeetinglecture a
                        left join tblpartyorgbranch b on a.BranchID=b.BranchID
                        where 1=1 ");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID=?BranchID ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = string.Format("{0}", branchId) });
            }

            sb.Append("and BeginTime<=now() order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        //课堂和会议的活动预告
        public static DataSet GetGridData2(int pageIndex, int pageSize, string branchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,Title,a.BranchID,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,BranchName,MeetType,case Category when 'Class' then 'tblPartyMyCourses' when 'Meeting' then 'tblPartyMyMeeting' when 'Class+Meeting' then 'tblPartyMyCourses' end as Category
                        from tblpartymeetinglecture a
                        left join tblpartyorgbranch b on a.BranchID=b.BranchID
                        where 1=1 ");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID=?BranchID ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = string.Format("{0}", branchId) });
            }

            sb.Append("and BeginTime>now() order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }






        /// <summary>
        /// 我的三会一课
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="title"></param>
        /// <param name="isMine"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static DataSet GetMyMeetingLecture(int pageIndex, int pageSize, string title, string isMine, string loginId, string category)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  meeting.ID,Title,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,BranchName,
                        case MeetType when '0' then '党员大会' when '1' then '支部委员会' when '2' then '党小组小组会' end as MeetingTypeName
                        from tblpartymeetinglecture meeting
                        left join tblpartyorgbranch branch on meeting.BranchID=branch.BranchID
                        left join tblpartymeetinglecturemember member on member.MeetingLectureID=meeting.ID
                        where 1=1 ");

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category like ?Category");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = string.Format("%{0}%", category) });
            }
            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and Title like ?Title");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(isMine))
            {
                sb.AppendFormat(" and (member.LoginID='{0}' and member.Status=5)", loginId);
            }
            else
            {
                sb.AppendFormat(" and member.LoginID='{0}'", loginId);
            }

            sb.Append(" order by meeting.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataTable GetMeetingCalendar(string loginID, string year, string month, string category)
        {
            string sql = string.Empty;
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            if (string.IsNullOrEmpty(category))
            {
                sql = @"call f_BranchID(?loginID);
select * FROM (select ID,Title,CONCAT(Title,'会议') as ModeName,'tblPartyMyMeeting' as TableName,BranchID,Address,BeginTime from tblpartymeetinglecture
 where Category='Meeting' and
DATE_FORMAT(BeginTime,'%Y')=?year and convert(DATE_FORMAT(BeginTime,'%m'),signed)=?month
UNION
select ID,Title,CONCAT(Title,'党课') as ModeName,'tblPartyMyCourses' as TableName,BranchID,Address,BeginTime from tblpartymeetinglecture
 where Category='Class' and
DATE_FORMAT(BeginTime,'%Y')=?year and convert(DATE_FORMAT(BeginTime,'%m'),signed)=?month
UNION
select ID,Title,CONCAT(Title,'党课+会议') as ModeName,'tblPartyMyCourses' as TableName,BranchID,Address,BeginTime from tblpartymeetinglecture
 where Category='Class+Meeting' and
DATE_FORMAT(BeginTime,'%Y')=?year and convert(DATE_FORMAT(BeginTime,'%m'),signed)=?month
)t
where BranchID in (select ID from tb_level);";
            }
            else if (category == "Meeting")
            {
                sql = @" call f_BranchID(?loginID);
select ID,Title,CONCAT(Title,'会议') as ModeName,'tblPartyMyMeeting' as TableName,Address,BeginTime from tblpartymeetinglecture
 where Category='Meeting' and
DATE_FORMAT(BeginTime,'%Y')=?year and convert(DATE_FORMAT(BeginTime,'%m'),signed)=?month
and BranchID in (select ID from tb_level) order by BeginTime;";
            }
            else if (category == "Courses")
            {
                sql = @"call f_BranchID(?loginID);
select ID,Title,CONCAT(Title,'党课') as ModeName,'tblPartyMyCourses' as TableName,Address,BeginTime from tblpartymeetinglecture
 where Category='Class' and
DATE_FORMAT(BeginTime,'%Y')=?year and convert(DATE_FORMAT(BeginTime,'%m'),signed)=?month
and BranchID in (select ID from tb_level) order by BeginTime;";
            }

            parameters.Add(new MySqlParameter() { ParameterName = "year", DbType = DbType.String, Value = year });
            parameters.Add(new MySqlParameter() { ParameterName = "month", DbType = DbType.String, Value = month });
            parameters.Add(new MySqlParameter() { ParameterName = "loginID", DbType = DbType.String, Value = loginID });
            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }

        public static DataTable GetDetail(string ID, string category)
        {
            string sql = string.Empty;
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            if (category == "tblPartyMyMeeting")
            {
                sql = @" select ID,Title,'会议' as ModeName,'tblPartyMyMeeting' as TableName,Address,BeginTime,EndTime,Record ,BranchID
from tblpartymeetinglecture where ID=?ID order by BeginTime";
            }
            else if (category == "tblPartyMyCourses")
            {
                sql = @" select ID,Title,'党课' as ModeName,'tblPartyMyCourses' as TableName,Address,BeginTime,EndTime,Record,BranchID
from tblpartymeetinglecture  where ID=?ID order by BeginTime";
            }

            parameters.Add(new MySqlParameter() { ParameterName = "ID", DbType = DbType.String, Value = ID });
            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }


        public static string GetCalendarDate(int year,int month, int day,string branchId)
        {
string sql = string.Empty;
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sql = @"select * from(
select str_to_date(BeginTime, '%Y-%m-%d') BeginTime
from tblpartymeetinglecture  m
where 
Year(BeginTime) = ?year and Month(BeginTime)=?month and day(BeginTime)=?day
and m.BranchID in (select ChildBranchID from tblpartyorgbranchrelation r where r.BranchID=?branchId) and m.Status = 2

) as TMP group by BeginTime";

            parameters.Add(new MySqlParameter() { ParameterName = "year", DbType = DbType.String, Value = year });
            parameters.Add(new MySqlParameter() { ParameterName = "month", DbType = DbType.String, Value = month });
            parameters.Add(new MySqlParameter() { ParameterName = "day", DbType = DbType.String, Value = day });
            parameters.Add(new MySqlParameter() { ParameterName = "branchId", DbType = DbType.String, Value = branchId });
            object o= MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            
            return (o!=DBNull.Value&& o != null) ? o.ToString() : "";
        }

        #endregion
    }
}
